package org.example.xiaomi.dao.impl;

import org.example.xiaomi.dao.CategoryDao;
import org.example.xiaomi.entity.Category;
import org.example.xiaomi.tx.TransactionManager;
import org.sql2o.Connection;
import org.sql2o.Query;

import java.util.List;
import java.util.Map;

/**
 * dao的实现类
 */

public class Sql2oCategoryDaoImpl implements CategoryDao {
    @Override
    public List<Category> find(int page, int limit, Map<String, Object> searchMap) {
        Connection connection = TransactionManager.getConnection();
        String sql = "select * from category where 1=1 ";
        if (searchMap != null && searchMap.size() > 0) {
            if (searchMap.get("name") != null && !"".equals(searchMap.get("name").toString())) {
                sql += " and name like concat('%',:name,'%') ";
            }
            if (searchMap.get("startTime") != null && !"".equals(searchMap.get("startTime").toString())) {
                sql += " and create_time >= :startTime ";
            }
            if (searchMap.get("endTime") != null && !"".equals(searchMap.get("endTime").toString())) {
                sql += " and create_time <= :endTime ";
            }
        }
        sql += " order by cid desc limit :offset,:pageSize ";
        Query query = connection.createQuery(sql);
        if (searchMap != null && searchMap.size() > 0) {
            if (searchMap.get("name") != null && !"".equals(searchMap.get("name").toString())) {
                query.addParameter("name", searchMap.get("name").toString());
            }
            if (searchMap.get("startTime") != null && !"".equals(searchMap.get("startTime").toString())) {
                query.addParameter("startTime", searchMap.get("startTime").toString());
            }
            if (searchMap.get("endTime") != null && !"".equals(searchMap.get("endTime").toString())) {
                query.addParameter("endTime", searchMap.get("endTime").toString());
            }
        }
        query.addParameter("offset", (page - 1) * limit);
        query.addParameter("pageSize", limit);
        return query.executeAndFetch(Category.class);
    }

    /**
     *
     * @param searchMap 搜索的数据
     * @return
     */
    @Override
    public long getCount(Map<String, Object> searchMap) {
        Connection connection = TransactionManager.getConnection();
        String sql = "select count(0) from category where 1=1 ";
        if (searchMap != null && searchMap.size() > 0) {
            if (searchMap.get("name") != null && !"".equals(searchMap.get("name").toString())) {
                sql += " and name like concat('%',:name,'%') ";
            }
            if (searchMap.get("startTime") != null && !"".equals(searchMap.get("startTime").toString())) {
                sql += " and create_time >= :startTime ";
            }
            if (searchMap.get("endTime") != null && !"".equals(searchMap.get("endTime").toString())) {
                sql += " and create_time <= :endTime ";
            }
        }
        Query query = connection.createQuery(sql);
        if (searchMap != null && searchMap.size() > 0) {
            if (searchMap.get("name") != null && !"".equals(searchMap.get("name").toString())) {
                query.addParameter("name", searchMap.get("name").toString());
            }
            if (searchMap.get("startTime") != null && !"".equals(searchMap.get("startTime").toString())) {
                query.addParameter("startTime", searchMap.get("startTime").toString());
            }
            if (searchMap.get("endTime") != null && !"".equals(searchMap.get("endTime").toString())) {
                query.addParameter("endTime", searchMap.get("endTime").toString());
            }
        }

        return query.executeAndFetchFirst(Long.class);
    }

    /**
     *
     * @param category 实体类
     * @return
     */
    @Override
    public int save(Category category) {
        return TransactionManager.getConnection()
                .createQuery("insert into  category(name, state, order_number, description, create_time) values (:name, :state, :order_number, :description, :create_time)")
                .addParameter("name", category.getName())
                .addParameter("state", category.getState())
                .addParameter("order_number", category.getOrder_number())
                .addParameter("description", category.getDescription())
                .addParameter("create_time", category.getCreate_time())
                .executeUpdate().getResult();
    }

    /**
     *
     * @param name 用户名，对比是否一致
     * @return
     */
    @Override
    public Category findByName(String name) {
        return TransactionManager.getConnection()
                .createQuery("select * from category where name = :name")
                .addParameter("name", name)
                .executeAndFetchFirst(Category.class);
    }

    /**
     *
     * @param category 商品类
     * @return
     */
    @Override
    public int update(Category category) {
        return TransactionManager.getConnection()
                .createQuery("update category set name=:name,order_number=:order_num, state=:state,description=:description where cid=:cid")
                .addParameter("name", category.getName())
                .addParameter("orader_num", category.getOrder_number())
                .addParameter("state", category.getState())
                .addParameter("cid", category.getCid())
                .addParameter("description", category.getDescription())
                .executeUpdate().getResult();
    }

    /**
     *
     * @param id 获得的删除商品id
     * @return
     */
    @Override
    public int delete(Long id) {
        return TransactionManager.getConnection()
                .createQuery("delete from category where cid=:cid ")
                .addParameter("cid", id)
                .executeUpdate().getResult();
    }


}
